﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ContasAPagarDAL
    Sub New()

    End Sub

    Function localizarAbastecimento(ByVal dataInicio As Date, ByVal datafinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select aba_codigo CODIGO , 'ABASTECIMENTO' DESCRICAO,aba_valor VALOR , aba_data DATA from abastecimento (nolock) where aba_data >= @dataInicio and aba_data <= @datafinal and aba_codigo not in (select aba_codigo from contas_pagar)"
            cmd.Parameters.Add(New SqlParameter("@dataInicio", SqlDbType.Date)).Value = dataInicio
            cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.Date)).Value = datafinal
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarOrdemServico(ByVal dataInicio As Date, ByVal datafinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select os_codigo CODIGO , 'ORDEM DE SERVIÇO' DESCRICAO , os_valor VALOR ,os_data DATA from ordem_servico (nolock) where os_data >= @dataInicio and os_data <= @dataFinal and os_codigo not in (select os_codigo from contas_pagar) and os_data_retorno > '01/01/2010' and os_valor <> 0"
            cmd.Parameters.Add(New SqlParameter("@dataInicio", SqlDbType.Date)).Value = dataInicio
            cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.Date)).Value = datafinal
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Sub inserirAbastecimento(ByVal aba_codigo As Integer, ByVal descricao As String, forma As String, datapgto As Date)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim id As Integer
        Try
            cmd.CommandText = "INSERT INTO contas_pagar([aba_codigo],[descricao_conta],[os_codigo],[cp_formapgto],[cp_databaixa])" &
                                    "VALUES(@aba_codigo,@descricao_conta,0,@forma,@datapgto)"
            cmd.Parameters.Add(New SqlParameter("@aba_codigo", SqlDbType.Int)).Value = aba_codigo
            cmd.Parameters.Add(New SqlParameter("@descricao_conta", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@forma", SqlDbType.VarChar)).Value = forma
            cmd.Parameters.Add(New SqlParameter("@datapgto", SqlDbType.Date)).Value = datapgto
            id = objD.executacomandoAI(cmd, "contas_pagar")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub inserirOrdem(ByVal os_codigo As Integer, ByVal descricao As String, forma As String, datapgto As Date)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim id As Integer
        Try
            cmd.CommandText = "INSERT INTO contas_pagar([os_codigo],[descricao_conta],[aba_codigo],[cp_formapgto],[cp_databaixa])" &
                                    "VALUES(@os_codigo,@descricao_conta,0,@forma,@datapgto)"
            cmd.Parameters.Add(New SqlParameter("@os_codigo", SqlDbType.Int)).Value = os_codigo
            cmd.Parameters.Add(New SqlParameter("@descricao_conta", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@forma", SqlDbType.VarChar)).Value = forma
            cmd.Parameters.Add(New SqlParameter("@datapgto", SqlDbType.Date)).Value = datapgto
            id = objD.executacomandoAI(cmd, "contas_pagar")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub


    Function localizarAbastecimentoBaixados(ByVal inicio As Date, ByVal fim As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select cp.cp_codigo REGISTRO,A.aba_codigo CODIGO , CP.descricao_conta DESCRICAO, aba_valor VALOR ,cp.cp_databaixa DATA,  cp.cp_formapgto FORMA_PGTO from abastecimento A, contas_pagar CP (nolock) where a.aba_codigo in (select aba_codigo from contas_pagar where cp_databaixa >=@inicio and cp_databaixa <=@fim)and a.aba_codigo = cp.aba_codigo"
            cmd.Parameters.Add(New SqlParameter("@inicio", SqlDbType.Date)).Value = inicio
            cmd.Parameters.Add(New SqlParameter("@fim", SqlDbType.Date)).Value = fim
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarOrdemServicoBaixados(ByVal inicio As Date, ByVal fim As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select cp.cp_codigo REGISTRO ,os.os_codigo CODIGO , cp.descricao_conta DESCRICAO , os_valor VALOR ,cp.cp_databaixa DATA , cp.cp_formapgto FORMA_PGTO from ordem_servico os ,contas_pagar cp (nolock) where os.os_codigo in (select os_codigo from contas_pagar where cp_databaixa >=@inicio and cp_databaixa <=@fim) and os.os_codigo = cp.os_codigo"
            cmd.Parameters.Add(New SqlParameter("@inicio", SqlDbType.Date)).Value = inicio
            cmd.Parameters.Add(New SqlParameter("@fim", SqlDbType.Date)).Value = fim
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function LocalizaGeral(inicio As Date, fim As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select cp.cp_codigo REGISTRO ,os.os_codigo CODIGO ,cp.descricao_conta DESCRICAO , os_valor VALOR ,cp.cp_databaixa DATA ,cp.cp_formapgto FORMA_PGTO from ordem_servico os ,contas_pagar cp (nolock) where os.os_codigo in (select os_codigo from contas_pagar where cp_databaixa >=@inicio and cp_databaixa <= @fim) and os.os_codigo = cp.os_codigo union " &
                              "select cp.cp_codigo REGISTRO ,A.aba_codigo CODIGO ,CP.descricao_conta DESCRICAO, aba_valor VALOR ,cp.cp_databaixa DATA,cp.cp_formapgto FORMA_PGTO from abastecimento A, contas_pagar CP (nolock) where a.aba_codigo in (select aba_codigo from contas_pagar where cp_databaixa >=@inicio and cp_databaixa <= @fim) and a.aba_codigo = cp.aba_codigo"
            cmd.Parameters.Add(New SqlParameter("@inicio", SqlDbType.Date)).Value = inicio
            cmd.Parameters.Add(New SqlParameter("@fim", SqlDbType.Date)).Value = fim
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function Localiza() As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select aba_codigo CODIGO , 'ABASTECIMENTO' DESCRICAO,aba_valor VALOR , aba_data DATA from abastecimento (nolock) where aba_codigo not in (select aba_codigo from contas_pagar) union " &
                                "select os_codigo CODIGO , 'ORDEM DE SERVIÇO' DESCRICAO , os_valor VALOR ,os_data DATA from ordem_servico (nolock) where os_codigo not in (select os_codigo from contas_pagar)"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

End Class
